package com.ljy.dao.impl;

import com.ljy.dao.LedgerDAO;
import com.ljy.pojo.Ledger;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class LedgerDAOImpl implements LedgerDAO {
    private static final String URL = "jdbc:mysql://192.168.3.48:3306/ledger_system?serverTimezone=UTC";
    private static final String USER = "yyx";
    private static final String PASSWORD = "YYXyyx123456";

    private Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }

    @Override
    public void addLedger(Ledger ledger) {
        String sql = "INSERT INTO ledger (serial_number, year, business_type, task_number, handover_number, " +
                "data_identifier, primary_directory, secondary_directory, data_name, control_plan_number, " +
                "control_plan_code, data_format, coordinate_system, coverage_area, coverage_area_size, " +
                "data_size_mb, file_count, is_confidential, submission_date, submitter, submission_department, " +
                "submission_medium, file_storage_location, spatial_storage_location, receiver, receipt_time, " +
                "storage_person, storage_description, auditor, audit_date) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        try (Connection conn = getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql)) {
            int serialNumber = getTotalRecords() + 1; // 自动生成序号
            pstmt.setInt(1, serialNumber);
            pstmt.setString(2, ledger.getYear());
            pstmt.setString(3, ledger.getBusinessType());
            pstmt.setString(4, ledger.getTaskNumber());
            pstmt.setString(5, ledger.getHandoverNumber());
            pstmt.setString(6, ledger.getDataIdentifier());
            pstmt.setString(7, ledger.getPrimaryDirectory());
            pstmt.setString(8, ledger.getSecondaryDirectory());
            pstmt.setString(9, ledger.getDataName());
            pstmt.setString(10, ledger.getControlPlanNumber());
            pstmt.setString(11, ledger.getControlPlanCode());
            pstmt.setString(12, ledger.getDataFormat());
            pstmt.setString(13, ledger.getCoordinateSystem());
            pstmt.setString(14, ledger.getCoverageArea());
            pstmt.setDouble(15, ledger.getCoverageAreaSize());
            pstmt.setDouble(16, ledger.getDataSizeMb());
            pstmt.setInt(17, ledger.getFileCount());
            pstmt.setBoolean(18, ledger.isConfidential());
            pstmt.setDate(19, ledger.getSubmissionDate() != null ? new java.sql.Date(ledger.getSubmissionDate().getTime()) : null);
            pstmt.setString(20, ledger.getSubmitter());
            pstmt.setString(21, ledger.getSubmissionDepartment());
            pstmt.setString(22, ledger.getSubmissionMedium());
            pstmt.setString(23, ledger.getFileStorageLocation());
            pstmt.setString(24, ledger.getSpatialStorageLocation());
            pstmt.setString(25, ledger.getReceiver());
            pstmt.setTimestamp(26, ledger.getReceiptTime() != null ? new Timestamp(ledger.getReceiptTime().getTime()) : null);
            pstmt.setString(27, ledger.getStoragePerson());
            pstmt.setString(28, ledger.getStorageDescription());
            pstmt.setString(29, ledger.getAuditor());
            pstmt.setDate(30, ledger.getAuditDate() != null ? new java.sql.Date(ledger.getAuditDate().getTime()) : null);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void deleteLedger(long id) {
        String sql = "DELETE FROM ledger WHERE id = ?";
        try (Connection conn = getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setLong(1, id);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public List<Ledger> getLedgers(int page, int rowsPerPage) {
        List<Ledger> ledgers = new ArrayList<>();
        String sql = "SELECT * FROM ledger ORDER BY serial_number LIMIT ? OFFSET ?";
        try (Connection conn = getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, rowsPerPage);
            pstmt.setInt(2, (page - 1) * rowsPerPage);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                ledgers.add(mapResultSetToLedger(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return ledgers;
    }

    @Override
    public int getTotalRecords() {
        String sql = "SELECT COUNT(*) FROM ledger";
        try (Connection conn = getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql)) {
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public List<Ledger> searchLedgers(String year, String businessType, String primaryDirectory,
                                      String secondaryDirectory, String startDate, String endDate) {
        List<Ledger> ledgers = new ArrayList<>();
        StringBuilder sql = new StringBuilder("SELECT * FROM ledger WHERE 1=1");
        List<Object> params = new ArrayList<>();
        if (year != null && !year.isEmpty()) {
            sql.append(" AND year = ?");
            params.add(year);
        }
        if (businessType != null && !businessType.isEmpty()) {
            sql.append(" AND business_type = ?");
            params.add(businessType);
        }
        if (primaryDirectory != null && !primaryDirectory.isEmpty()) {
            sql.append(" AND primary_directory = ?");
            params.add(primaryDirectory);
        }
        if (secondaryDirectory != null && !secondaryDirectory.isEmpty()) {
            sql.append(" AND secondary_directory = ?");
            params.add(secondaryDirectory);
        }
        if (startDate != null && !startDate.isEmpty()) {
            sql.append(" AND submission_date >= ?");
            params.add(startDate);
        }
        if (endDate != null && !endDate.isEmpty()) {
            sql.append(" AND submission_date <= ?");
            params.add(endDate);
        }
        try (Connection conn = getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql.toString())) {
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(i + 1, params.get(i));
            }
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                ledgers.add(mapResultSetToLedger(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return ledgers;
    }

    @Override
    public boolean existsById(long id) {
        if (id <= 0) return false; // id 为 0 表示新记录
        String sql = "SELECT COUNT(*) FROM ledger WHERE id = ?";
        try (Connection conn = getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setLong(1, id);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    @Override
    public void saveAllLedgers(List<Ledger> ledgers) {
        String insertSql = "INSERT INTO ledger (serial_number, year, business_type, task_number, handover_number, " +
                "data_identifier, primary_directory, secondary_directory, data_name, control_plan_number, " +
                "control_plan_code, data_format, coordinate_system, coverage_area, coverage_area_size, " +
                "data_size_mb, file_count, is_confidential, submission_date, submitter, submission_department, " +
                "submission_medium, file_storage_location, spatial_storage_location, receiver, receipt_time, " +
                "storage_person, storage_description, auditor, audit_date) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        try (Connection conn = getConnection()) {
            conn.setAutoCommit(false);
            try (PreparedStatement insertStmt = conn.prepareStatement(insertSql)) {
                int newSerialNumber = getTotalRecords() + 1;
                for (Ledger ledger : ledgers) {
                    if (ledger.getId() == 0 || !existsById(ledger.getId())) {
                        // 新记录：插入
                        insertStmt.setInt(1, ledger.getSerialNumber() > 0 ? ledger.getSerialNumber() : newSerialNumber++);
                        insertStmt.setString(2, ledger.getYear());
                        insertStmt.setString(3, ledger.getBusinessType());
                        insertStmt.setString(4, ledger.getTaskNumber());
                        insertStmt.setString(5, ledger.getHandoverNumber());
                        insertStmt.setString(6, ledger.getDataIdentifier());
                        insertStmt.setString(7, ledger.getPrimaryDirectory());
                        insertStmt.setString(8, ledger.getSecondaryDirectory());
                        insertStmt.setString(9, ledger.getDataName());
                        insertStmt.setString(10, ledger.getControlPlanNumber());
                        insertStmt.setString(11, ledger.getControlPlanCode());
                        insertStmt.setString(12, ledger.getDataFormat());
                        insertStmt.setString(13, ledger.getCoordinateSystem());
                        insertStmt.setString(14, ledger.getCoverageArea());
                        insertStmt.setDouble(15, ledger.getCoverageAreaSize());
                        insertStmt.setDouble(16, ledger.getDataSizeMb());
                        insertStmt.setInt(17, ledger.getFileCount());
                        insertStmt.setBoolean(18, ledger.isConfidential());
                        insertStmt.setDate(19, ledger.getSubmissionDate() != null ? new java.sql.Date(ledger.getSubmissionDate().getTime()) : null);
                        insertStmt.setString(20, ledger.getSubmitter());
                        insertStmt.setString(21, ledger.getSubmissionDepartment());
                        insertStmt.setString(22, ledger.getSubmissionMedium());
                        insertStmt.setString(23, ledger.getFileStorageLocation());
                        insertStmt.setString(24, ledger.getSpatialStorageLocation());
                        insertStmt.setString(25, ledger.getReceiver());
                        insertStmt.setTimestamp(26, ledger.getReceiptTime() != null ? new Timestamp(ledger.getReceiptTime().getTime()) : null);
                        insertStmt.setString(27, ledger.getStoragePerson());
                        insertStmt.setString(28, ledger.getStorageDescription());
                        insertStmt.setString(29, ledger.getAuditor());
                        insertStmt.setDate(30, ledger.getAuditDate() != null ? new java.sql.Date(ledger.getAuditDate().getTime()) : null);
                        insertStmt.addBatch();
                    } else {
                        // 已有记录：更新
                        updateLedger(ledger);
                    }
                }
                insertStmt.executeBatch();
                conn.commit();
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("保存记录失败: " + e.getMessage());
        }
    }

    @Override
    public void importFromExcel(String filePath) {
        List<Ledger> ledgers = new ArrayList<>();
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
        try (FileInputStream fis = new FileInputStream(filePath);
             Workbook workbook = new XSSFWorkbook(fis)) {
            Sheet sheet = workbook.getSheetAt(0);
            Row headerRow = sheet.getRow(0);
            if (headerRow == null) {
                throw new IllegalArgumentException("Excel 文件缺少表头");
            }

            // 验证表头
            String[] expectedHeaders = {
                    "序号", "年份", "业务类别", "任务编号", "交接单序号", "数据标识", "数据一级目录", "数据二级目录",
                    "数据名称", "控规名称/核定表编号/历史遗留问题编号", "控规代号", "数据格式", "数据坐标系",
                    "覆盖区域", "覆盖面积(㎡)", "数据量大小(MB)", "文件个数", "数据是否涉密", "提交日期",
                    "提交人", "提交部门", "提交介质/提交位置", "文件入库位置", "空间入库位置", "接收人",
                    "接收时间", "入库人", "入库情况说明", "审核人", "审核日期"
            };
            for (int i = 0; i < expectedHeaders.length; i++) {
                Cell cell = headerRow.getCell(i);
                String header = cell != null ? cell.getStringCellValue() : "";
                if (!expectedHeaders[i].equals(header)) {
                    throw new IllegalArgumentException("表头不匹配，期望: " + expectedHeaders[i] + ", 实际: " + header);
                }
            }

            // 读取数据行（从第 2 行开始）
            for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                Row row = sheet.getRow(rowIndex);
                if (row == null) continue;

                Ledger ledger = new Ledger();
                try {
                    // 序号 (serial_number, int)
                    Cell cell = row.getCell(0);
                    if (cell != null && cell.getCellType() == CellType.NUMERIC) {
                        ledger.setSerialNumber((int) cell.getNumericCellValue());
                    } else {
                        throw new IllegalArgumentException("序号必须为整数");
                    }

                    // 年份 (year, String)
                    cell = row.getCell(1);
                    ledger.setYear(cell != null ? cell.getStringCellValue() : "");

                    // 业务类别 (business_type, String)
                    cell = row.getCell(2);
                    ledger.setBusinessType(cell != null ? cell.getStringCellValue() : "");

                    // 任务编号 (task_number, String)
                    cell = row.getCell(3);
                    ledger.setTaskNumber(cell != null ? cell.getStringCellValue() : "");

                    // 交接单序号 (handover_number, String)
                    cell = row.getCell(4);
                    ledger.setHandoverNumber(cell != null ? cell.getStringCellValue() : "");

                    // 数据标识 (data_identifier, String)
                    cell = row.getCell(5);
                    ledger.setDataIdentifier(cell != null ? cell.getStringCellValue() : "");

                    // 数据一级目录 (primary_directory, String)
                    cell = row.getCell(6);
                    ledger.setPrimaryDirectory(cell != null ? cell.getStringCellValue() : "");

                    // 数据二级目录 (secondary_directory, String)
                    cell = row.getCell(7);
                    ledger.setSecondaryDirectory(cell != null ? cell.getStringCellValue() : "");

                    // 数据名称 (data_name, String)
                    cell = row.getCell(8);
                    ledger.setDataName(cell != null ? cell.getStringCellValue() : "");

                    // 控规名称/核定表编号/历史遗留问题编号 (control_plan_number, String)
                    cell = row.getCell(9);
                    ledger.setControlPlanNumber(cell != null ? cell.getStringCellValue() : "");

                    // 控规代号 (control_plan_code, String)
                    cell = row.getCell(10);
                    ledger.setControlPlanCode(cell != null ? cell.getStringCellValue() : "");

                    // 数据格式 (data_format, String)
                    cell = row.getCell(11);
                    ledger.setDataFormat(cell != null ? cell.getStringCellValue() : "");

                    // 数据坐标系 (coordinate_system, String)
                    cell = row.getCell(12);
                    ledger.setCoordinateSystem(cell != null ? cell.getStringCellValue() : "");

                    // 覆盖区域 (coverage_area, String)
                    cell = row.getCell(13);
                    ledger.setCoverageArea(cell != null ? cell.getStringCellValue() : "");

                    // 覆盖面积(㎡) (coverage_area_size, double)
                    cell = row.getCell(14);
                    if (cell != null && cell.getCellType() == CellType.NUMERIC) {
                        ledger.setCoverageAreaSize(cell.getNumericCellValue());
                    } else {
                        ledger.setCoverageAreaSize(0.0);
                    }

                    // 数据量大小(MB) (data_size_mb, double)
                    cell = row.getCell(15);
                    if (cell != null && cell.getCellType() == CellType.NUMERIC) {
                        ledger.setDataSizeMb(cell.getNumericCellValue());
                    } else {
                        ledger.setDataSizeMb(0.0);
                    }

                    // 文件个数 (file_count, int)
                    cell = row.getCell(16);
                    if (cell != null && cell.getCellType() == CellType.NUMERIC) {
                        ledger.setFileCount((int) cell.getNumericCellValue());
                    } else {
                        throw new IllegalArgumentException("文件个数必须为整数");
                    }

                    // 数据是否涉密 (is_confidential, boolean)
                    cell = row.getCell(17);
                    if (cell != null) {
                        if (cell.getCellType() == CellType.BOOLEAN) {
                            ledger.setConfidential(cell.getBooleanCellValue());
                        } else if (cell.getCellType() == CellType.STRING) {
                            String value = cell.getStringCellValue();
                            ledger.setConfidential(value.equals("是") || value.equalsIgnoreCase("true"));
                        } else {
                            throw new IllegalArgumentException("数据是否涉密必须为布尔值或“是”/“否”");
                        }
                    } else {
                        ledger.setConfidential(false);
                    }

                    // 提交日期 (submission_date, Date)
                    cell = row.getCell(18);
                    if (cell != null && !cell.getStringCellValue().isEmpty()) {
                        try {
                            ledger.setSubmissionDate(dateFormat.parse(cell.getStringCellValue()));
                        } catch (Exception e) {
                            throw new IllegalArgumentException("提交日期格式错误: " + cell.getStringCellValue());
                        }
                    }

                    // 提交人 (submitter, String)
                    cell = row.getCell(19);
                    ledger.setSubmitter(cell != null ? cell.getStringCellValue() : "");

                    // 提交部门 (submission_department, String)
                    cell = row.getCell(20);
                    ledger.setSubmissionDepartment(cell != null ? cell.getStringCellValue() : "");

                    // 提交介质/提交位置 (submission_medium, String)
                    cell = row.getCell(21);
                    ledger.setSubmissionMedium(cell != null ? cell.getStringCellValue() : "");

                    // 文件入库位置 (file_storage_location, String)
                    cell = row.getCell(22);
                    ledger.setFileStorageLocation(cell != null ? cell.getStringCellValue() : "");

                    // 空间入库位置 (spatial_storage_location, String)
                    cell = row.getCell(23);
                    ledger.setSpatialStorageLocation(cell != null ? cell.getStringCellValue() : "");

                    // 接收人 (receiver, String)
                    cell = row.getCell(24);
                    ledger.setReceiver(cell != null ? cell.getStringCellValue() : "");

                    // 接收时间 (receipt_time, Date)
                    cell = row.getCell(25);
                    if (cell != null && !cell.getStringCellValue().isEmpty()) {
                        try {
                            ledger.setReceiptTime(dateFormat.parse(cell.getStringCellValue()));
                        } catch (Exception e) {
                            throw new IllegalArgumentException("接收时间格式错误: " + cell.getStringCellValue());
                        }
                    }

                    // 入库人 (storage_person, String)
                    cell = row.getCell(26);
                    ledger.setStoragePerson(cell != null ? cell.getStringCellValue() : "");

                    // 入库情况说明 (storage_description, String)
                    cell = row.getCell(27);
                    ledger.setStorageDescription(cell != null ? cell.getStringCellValue() : "");

                    // 审核人 (auditor, String)
                    cell = row.getCell(28);
                    ledger.setAuditor(cell != null ? cell.getStringCellValue() : "");

                    // 审核日期 (audit_date, Date)
                    cell = row.getCell(29);
                    if (cell != null && !cell.getStringCellValue().isEmpty()) {
                        try {
                            ledger.setAuditDate(dateFormat.parse(cell.getStringCellValue()));
                        } catch (Exception e) {
                            throw new IllegalArgumentException("审核日期格式错误: " + cell.getStringCellValue());
                        }
                    }

                    ledgers.add(ledger);
                } catch (IllegalArgumentException e) {
                    throw new IllegalArgumentException("第 " + (rowIndex + 1) + " 行数据错误: " + e.getMessage());
                }
            }

            // 插入数据库
            String sql = "INSERT INTO ledger (" +
                    "serial_number, year, business_type, task_number, handover_number, data_identifier, " +
                    "primary_directory, secondary_directory, data_name, control_plan_number, control_plan_code, " +
                    "data_format, coordinate_system, coverage_area, coverage_area_size, data_size_mb, file_count, " +
                    "is_confidential, submission_date, submitter, submission_department, submission_medium, " +
                    "file_storage_location, spatial_storage_location, receiver, receipt_time, storage_person, " +
                    "storage_description, auditor, audit_date) " +
                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            try (Connection conn = getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql)) {
                for (Ledger ledger : ledgers) {
                    pstmt.setInt(1, ledger.getSerialNumber());
                    pstmt.setString(2, ledger.getYear());
                    pstmt.setString(3, ledger.getBusinessType());
                    pstmt.setString(4, ledger.getTaskNumber());
                    pstmt.setString(5, ledger.getHandoverNumber());
                    pstmt.setString(6, ledger.getDataIdentifier());
                    pstmt.setString(7, ledger.getPrimaryDirectory());
                    pstmt.setString(8, ledger.getSecondaryDirectory());
                    pstmt.setString(9, ledger.getDataName());
                    pstmt.setString(10, ledger.getControlPlanNumber());
                    pstmt.setString(11, ledger.getControlPlanCode());
                    pstmt.setString(12, ledger.getDataFormat());
                    pstmt.setString(13, ledger.getCoordinateSystem());
                    pstmt.setString(14, ledger.getCoverageArea());
                    pstmt.setDouble(15, ledger.getCoverageAreaSize());
                    pstmt.setDouble(16, ledger.getDataSizeMb());
                    pstmt.setInt(17, ledger.getFileCount());
                    pstmt.setBoolean(18, ledger.isConfidential());
                    pstmt.setDate(19, ledger.getSubmissionDate() != null ? new java.sql.Date(ledger.getSubmissionDate().getTime()) : null);
                    pstmt.setString(20, ledger.getSubmitter());
                    pstmt.setString(21, ledger.getSubmissionDepartment());
                    pstmt.setString(22, ledger.getSubmissionMedium());
                    pstmt.setString(23, ledger.getFileStorageLocation());
                    pstmt.setString(24, ledger.getSpatialStorageLocation());
                    pstmt.setString(25, ledger.getReceiver());
                    pstmt.setTimestamp(26, ledger.getReceiptTime() != null ? new java.sql.Timestamp(ledger.getReceiptTime().getTime()) : null);
                    pstmt.setString(27, ledger.getStoragePerson());
                    pstmt.setString(28, ledger.getStorageDescription());
                    pstmt.setString(29, ledger.getAuditor());
                    pstmt.setDate(30, ledger.getAuditDate() != null ? new java.sql.Date(ledger.getAuditDate().getTime()) : null);
                    pstmt.addBatch();
                }
                pstmt.executeBatch();
                System.out.println("成功导入 " + ledgers.size() + " 条记录");
            } catch (SQLException e) {
                throw new RuntimeException("插入数据库失败: " + e.getMessage());
            }
        } catch (IOException e) {
            throw new RuntimeException("读取 Excel 文件失败: " + e.getMessage());
        } catch (IllegalArgumentException e) {
            throw new RuntimeException("Excel 数据格式错误: " + e.getMessage());
        }
    }

    @Override
    public void exportToExcel(List<Ledger> ledgers, String filePath) {
        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("Ledger Data");
            // 创建表头
            Row headerRow = sheet.createRow(0);
            String[] columns = {
                    "序号", "年份", "业务类别", "任务编号", "交接单序号", "数据标识", "数据一级目录", "数据二级目录",
                    "数据名称", "控规名称/核定表编号/历史遗留问题编号", "控规代号", "数据格式", "数据坐标系",
                    "覆盖区域", "覆盖面积(㎡)", "数据量大小(MB)", "文件个数", "数据是否涉密", "提交日期",
                    "提交人", "提交部门", "提交介质/提交位置", "文件入库位置", "空间入库位置", "接收人",
                    "接收时间", "入库人", "入库情况说明", "审核人", "审核日期"
            };
            for (int i = 0; i < columns.length; i++) {
                headerRow.createCell(i).setCellValue(columns[i]);
            }
            // 填充数据
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
            for (int i = 0; i < ledgers.size(); i++) {
                Row row = sheet.createRow(i + 1);
                Ledger ledger = ledgers.get(i);
                row.createCell(0).setCellValue(ledger.getSerialNumber());
                row.createCell(1).setCellValue(ledger.getYear());
                row.createCell(2).setCellValue(ledger.getBusinessType());
                row.createCell(3).setCellValue(ledger.getTaskNumber());
                row.createCell(4).setCellValue(ledger.getHandoverNumber());
                row.createCell(5).setCellValue(ledger.getDataIdentifier());
                row.createCell(6).setCellValue(ledger.getPrimaryDirectory());
                row.createCell(7).setCellValue(ledger.getSecondaryDirectory());
                row.createCell(8).setCellValue(ledger.getDataName());
                row.createCell(9).setCellValue(ledger.getControlPlanNumber());
                row.createCell(10).setCellValue(ledger.getControlPlanCode());
                row.createCell(11).setCellValue(ledger.getDataFormat());
                row.createCell(12).setCellValue(ledger.getCoordinateSystem());
                row.createCell(13).setCellValue(ledger.getCoverageArea());
                row.createCell(14).setCellValue(ledger.getCoverageAreaSize());
                row.createCell(15).setCellValue(ledger.getDataSizeMb());
                row.createCell(16).setCellValue(ledger.getFileCount());
                row.createCell(17).setCellValue(ledger.isConfidential());
                row.createCell(18).setCellValue(ledger.getSubmissionDate() != null ? dateFormat.format(ledger.getSubmissionDate()) : "");
                row.createCell(19).setCellValue(ledger.getSubmitter());
                row.createCell(20).setCellValue(ledger.getSubmissionDepartment());
                row.createCell(21).setCellValue(ledger.getSubmissionMedium());
                row.createCell(22).setCellValue(ledger.getFileStorageLocation());
                row.createCell(23).setCellValue(ledger.getSpatialStorageLocation());
                row.createCell(24).setCellValue(ledger.getReceiver());
                row.createCell(25).setCellValue(ledger.getReceiptTime() != null ? dateFormat.format(ledger.getReceiptTime()) : "");
                row.createCell(26).setCellValue(ledger.getStoragePerson());
                row.createCell(27).setCellValue(ledger.getStorageDescription());
                row.createCell(28).setCellValue(ledger.getAuditor());
                row.createCell(29).setCellValue(ledger.getAuditDate() != null ? dateFormat.format(ledger.getAuditDate()) : "");
            }
            try (FileOutputStream fos = new FileOutputStream(filePath)) {
                workbook.write(fos);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private Ledger mapResultSetToLedger(ResultSet rs) throws SQLException {
        Ledger ledger = new Ledger();
        ledger.setId(rs.getLong("id"));
        ledger.setSerialNumber(rs.getInt("serial_number"));
        ledger.setYear(rs.getString("year"));
        ledger.setBusinessType(rs.getString("business_type"));
        ledger.setTaskNumber(rs.getString("task_number"));
        ledger.setHandoverNumber(rs.getString("handover_number"));
        ledger.setDataIdentifier(rs.getString("data_identifier"));
        ledger.setPrimaryDirectory(rs.getString("primary_directory"));
        ledger.setSecondaryDirectory(rs.getString("secondary_directory"));
        ledger.setDataName(rs.getString("data_name"));
        ledger.setControlPlanNumber(rs.getString("control_plan_number"));
        ledger.setControlPlanCode(rs.getString("control_plan_code"));
        ledger.setDataFormat(rs.getString("data_format"));
        ledger.setCoordinateSystem(rs.getString("coordinate_system"));
        ledger.setCoverageArea(rs.getString("coverage_area"));
        ledger.setCoverageAreaSize(rs.getDouble("coverage_area_size"));
        ledger.setDataSizeMb(rs.getDouble("data_size_mb"));
        ledger.setFileCount(rs.getInt("file_count"));
        ledger.setConfidential(rs.getBoolean("is_confidential"));
        ledger.setSubmissionDate(rs.getDate("submission_date"));
        ledger.setSubmitter(rs.getString("submitter"));
        ledger.setSubmissionDepartment(rs.getString("submission_department"));
        ledger.setSubmissionMedium(rs.getString("submission_medium"));
        ledger.setFileStorageLocation(rs.getString("file_storage_location"));
        ledger.setSpatialStorageLocation(rs.getString("spatial_storage_location"));
        ledger.setReceiver(rs.getString("receiver"));
        ledger.setReceiptTime(rs.getTimestamp("receipt_time"));
        ledger.setStoragePerson(rs.getString("storage_person"));
        ledger.setStorageDescription(rs.getString("storage_description"));
        ledger.setAuditor(rs.getString("auditor"));
        ledger.setAuditDate(rs.getDate("audit_date"));
        return ledger;
    }

    private Object getCellValue(Cell cell) {
        if (cell == null) return "";
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue();
                }
                return cell.getNumericCellValue();
            case BOOLEAN:
                return cell.getBooleanCellValue();
            default:
                return "";
        }
    }

    @Override
    public List<Ledger> getAllLedgers() {
        List<Ledger> ledgers = new ArrayList<>();
        String sql = "SELECT * FROM ledger ORDER BY serial_number";
        try (Connection conn = getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql)) {
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                ledgers.add(mapResultSetToLedger(rs));
            }
        } catch (SQLException e) {
            System.err.println("查询所有记录失败: " + e.getMessage());
            e.printStackTrace();
        }
        return ledgers;
    }

    @Override
    public void updateLedger(Ledger ledger) {
        String sql = "UPDATE ledger SET serial_number = ?, year = ?, business_type = ?, task_number = ?, " +
                "handover_number = ?, data_identifier = ?, primary_directory = ?, secondary_directory = ?, " +
                "data_name = ?, control_plan_number = ?, control_plan_code = ?, data_format = ?, " +
                "coordinate_system = ?, coverage_area = ?, coverage_area_size = ?, data_size_mb = ?, " +
                "file_count = ?, is_confidential = ?, submission_date = ?, submitter = ?, " +
                "submission_department = ?, submission_medium = ?, file_storage_location = ?, " +
                "spatial_storage_location = ?, receiver = ?, receipt_time = ?, storage_person = ?, " +
                "storage_description = ?, auditor = ?, audit_date = ? WHERE id = ?";
        try (Connection conn = getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, ledger.getSerialNumber());
            pstmt.setString(2, ledger.getYear());
            pstmt.setString(3, ledger.getBusinessType());
            pstmt.setString(4, ledger.getTaskNumber());
            pstmt.setString(5, ledger.getHandoverNumber());
            pstmt.setString(6, ledger.getDataIdentifier());
            pstmt.setString(7, ledger.getPrimaryDirectory());
            pstmt.setString(8, ledger.getSecondaryDirectory());
            pstmt.setString(9, ledger.getDataName());
            pstmt.setString(10, ledger.getControlPlanNumber());
            pstmt.setString(11, ledger.getControlPlanCode());
            pstmt.setString(12, ledger.getDataFormat());
            pstmt.setString(13, ledger.getCoordinateSystem());
            pstmt.setString(14, ledger.getCoverageArea());
            pstmt.setDouble(15, ledger.getCoverageAreaSize());
            pstmt.setDouble(16, ledger.getDataSizeMb());
            pstmt.setInt(17, ledger.getFileCount());
            pstmt.setBoolean(18, ledger.isConfidential());
            pstmt.setDate(19, ledger.getSubmissionDate() != null ? new java.sql.Date(ledger.getSubmissionDate().getTime()) : null);
            pstmt.setString(20, ledger.getSubmitter());
            pstmt.setString(21, ledger.getSubmissionDepartment());
            pstmt.setString(22, ledger.getSubmissionMedium());
            pstmt.setString(23, ledger.getFileStorageLocation());
            pstmt.setString(24, ledger.getSpatialStorageLocation());
            pstmt.setString(25, ledger.getReceiver());
            pstmt.setTimestamp(26, ledger.getReceiptTime() != null ? new Timestamp(ledger.getReceiptTime().getTime()) : null);
            pstmt.setString(27, ledger.getStoragePerson());
            pstmt.setString(28, ledger.getStorageDescription());
            pstmt.setString(29, ledger.getAuditor());
            pstmt.setDate(30, ledger.getAuditDate() != null ? new java.sql.Date(ledger.getAuditDate().getTime()) : null);
            pstmt.setLong(31, ledger.getId());
            int rowsAffected = pstmt.executeUpdate();
            if (rowsAffected == 0) {
                throw new SQLException("更新记录失败：ID " + ledger.getId() + " 不存在");
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("更新记录失败: " + e.getMessage());
        }
    }
}